using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;

namespace OgilvyOne.SqlServerDAL
{
    public class Resume : IResume
    {
        // Methods
        public bool Add_Resume_data(ResumeInfo resumeinfo)
        {
            bool flag = false;
            string cmdText = "insert into RESUME_INFO (RESUME_file, duty, create_date, isdel) values (@resume_file, @duty, getdate(), 0)";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@resume_file", SqlDbType.VarChar, 0xff), new SqlParameter("@duty", SqlDbType.VarChar, 100) };
            cmdParms[0].Value = resumeinfo.Resume_File;
            cmdParms[1].Value = resumeinfo.Duty;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        flag = true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                    return flag;
                }
            }
        }

        public void Delete(int resume_id)
        {
            string cmdText = "update RESUME_INFO set ISDEL='1' where RESUME_ID=@resume_id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@resume_id", SqlDbType.Int, 4) };
            cmdParms[0].Value = resume_id;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public IList getResumeList(ResumeInfo resumeInfo)
        {
            IList list = new ArrayList();
            string cmdText = "select RESUME_ID,RESUME_file,Duty from  RESUME_INFO where ISDEL='0'";
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                while (reader.Read())
                {
                    ResumeInfo info = new ResumeInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
                    list.Add(info);
                }
            }
            return list;
        }

        public ResumeInfo getResumer_data(int resume_id)
        {
            ResumeInfo info = null;
            string str = "select RESUME_ID,RESUME_file,Duty,Create_Date from  RESUME_INFO where  ISDEL='0' and RESUME_ID=";
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, str + resume_id, null))
            {
                while (reader.Read())
                {
                    info = new ResumeInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
                    info.Create_Data = reader.GetSqlDateTime(3).ToString();
                }
            }
            return info;
        }

        public IList select_Resumer_data(int resume_id)
        {
            IList list = new ArrayList();
            string str = "select RESUME_ID,RESUME_file,Duty,Create_Date from  RESUME_INFO where isdel='0' and RESUME_ID=";
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, str + resume_id, null))
            {
                while (reader.Read())
                {
                    ResumeInfo info = new ResumeInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
                    info.Create_Data = reader.GetSqlDateTime(3).ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        public IList select_Resumer_data(string duty)
        {
            IList list = new ArrayList();
            string format = "select RESUME_ID,RESUME_file,Duty,Create_Date from  RESUME_INFO where isdel='0'and Duty LIKE '%{0}%'";
            string cmdText = string.Format(format, duty);
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                while (reader.Read())
                {
                    ResumeInfo info = new ResumeInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
                    info.Create_Data = reader.GetSqlDateTime(3).ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        public bool update_Resume_data(ResumeInfo resumeinfo)
        {
            bool flag = false;
            string cmdText = "update  RESUME_INFO set RESUME_file=@resume_file,Duty=@duty where RESUME_ID=@resume_id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@resume_file", SqlDbType.VarChar, 0xff), new SqlParameter("@duty", SqlDbType.VarChar, 100), new SqlParameter("@resume_id", SqlDbType.Int, 4) };
            cmdParms[0].Value = resumeinfo.Resume_File;
            cmdParms[1].Value = resumeinfo.Duty;
            cmdParms[2].Value = resumeinfo.Resume_ID;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        flag = true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                    return flag;
                }
            }
        }
    }
}